﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace tsgl
{
    class bookDbClass
    {
        public static int ExecuteNonQurey(string sql, params SqlParameter[] parameters)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    DataSet dataset = new DataSet();
                    SqlDataAdapter adaper = new SqlDataAdapter(cmd);
                    adaper.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }

        public static DataTable createDataTableByKey(string fieldName,string tableName, string conditionStr,string dataTableName)
        {
            conditionStr = "";
            dataTableName = "dataTable0";
            string commStr;
            if (conditionStr == "")
            {
                commStr = "select {0} from {1}";
                commStr = string.Format(commStr,fieldName,tableName);
            }
            else
            {
                commStr = "select {0} from {1} where {2}";
                commStr = string.Format(fieldName,tableName,conditionStr);
            }
            
            string connstr=ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlDataAdapter ad = new SqlDataAdapter(commStr,conn);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                conn.Close();
                return ds.Tables[dataTableName];
            }
        }

        public static Boolean editBySql(string strSql)
        {
            string returnStr = updateDataTable(strSql);
            if (returnStr == "Y")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static Boolean insertBySql(string strSql)
        {
            string returenStr = updateDataTable(strSql);
            if (returenStr == "Y")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static string updateDataTable(string commStr)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(commStr,conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return "Y";
            }
        }

        public static Boolean deleteByKey(string tableName,string keyName,string keyValue)
        {
            string sql = "delete from {0} where {1} = {2}";
            sql = string.Format(sql,tableName,keyName,keyValue);
            string returenStr = updateDataTable(sql);
            if (returenStr == "Y")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static Boolean deleteBySql(string strSql)
        {
            string returenStr = updateDataTable(strSql);
            if (returenStr == "Y")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static Boolean deleteByDt(int id,string sql)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                DataSet ds = new DataSet();
                string sqlset = sql;
                SqlDataAdapter da = new SqlDataAdapter(sqlset, conn);
                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                dt.Rows[id].Delete();
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.Update(ds);
            }
            return true;
        }

        public static Boolean userPermissionDelete(string userTypeId)
        {
            if (bookDbClass.deleteByKey("用户权限", "用户类型名称", userTypeId) == true)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static Boolean userPermissionAdd(string userTypeName, string userPermission)
        {
            string strInsertComm = "insert into 用户权限(用户类型名称,用户权限选项) values({0},{1})";
            strInsertComm = string.Format(strInsertComm,userTypeName,userPermission);
            return bookDbClass.insertBySql(strInsertComm);
        }


    }
}
